using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MovieDBReverseEngineering
{
    /// <summary>
    /// 使用EF Core上下文类对电影数据库进行增删改查操作的实现
    /// 提供同步和异步方法
    /// </summary>
    public class MovieDBContextOperations : IDisposable
    {
        private MovieDBContext _context;
        private bool _disposed = false;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="useMySql">是否使用MySQL数据库（默认为true）</param>
        public MovieDBContextOperations(string connectionString, bool useMySql = true)
        {
            var optionsBuilder = new DbContextOptionsBuilder<MovieDBContext>();
            
            if (useMySql)
            {
                // 使用MySQL数据库
                // 注意：需要安装Pomelo.EntityFrameworkCore.MySql包
                optionsBuilder.UseMySql(connectionString, new MySqlServerVersion(new Version(8, 0, 21)));
            }
            else
            {
                // 使用SQL Server数据库
                optionsBuilder.UseSqlServer(connectionString);
            }
            
            _context = new MovieDBContext(optionsBuilder.Options);
        }

        #region 艺人相关操作

        /// <summary>
        /// 添加艺人（同步）
        /// </summary>
        /// <param name="artist">艺人对象</param>
        /// <returns>添加的艺人ID</returns>
        public int AddArtist(Artist artist)
        {            
            try
            {                
                ValidateArtist(artist);
                _context.Artists.Add(artist);
                _context.SaveChanges();
                return artist.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 添加艺人（异步）
        /// </summary>
        /// <param name="artist">艺人对象</param>
        /// <returns>添加的艺人ID</returns>
        public async Task<int> AddArtistAsync(Artist artist)
        {            
            try
            {                
                ValidateArtist(artist);
                await _context.Artists.AddAsync(artist);
                await _context.SaveChangesAsync();
                return artist.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有艺人（同步）
        /// </summary>
        /// <returns>艺人列表</returns>
        public List<Artist> GetAllArtists()
        {            
            try
            {                
                return _context.Artists.ToList();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有艺人（异步）
        /// </summary>
        /// <returns>艺人列表</returns>
        public async Task<List<Artist>> GetAllArtistsAsync()
        {            
            try
            {                
                return await _context.Artists.ToListAsync();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取艺人（同步）
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>艺人对象，如果不存在则返回null</returns>
        public Artist GetArtistById(int id)
        {            
            try
            {                
                return _context.Artists.FirstOrDefault(a => a.Id == id);
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取艺人（异步）
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>艺人对象，如果不存在则返回null</returns>
        public async Task<Artist> GetArtistByIdAsync(int id)
        {            
            try
            {                
                return await _context.Artists.FirstOrDefaultAsync(a => a.Id == id);
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新艺人信息（同步）
        /// </summary>
        /// <param name="artist">艺人对象</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateArtist(Artist artist)
        {            
            try
            {                
                ValidateArtist(artist);
                var existingArtist = _context.Artists.Find(artist.Id);
                if (existingArtist == null)
                {
                    return false;
                }
                
                // 更新艺人信息
                _context.Entry(existingArtist).CurrentValues.SetValues(artist);
                _context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"更新艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新艺人信息（异步）
        /// </summary>
        /// <param name="artist">艺人对象</param>
        /// <returns>是否更新成功</returns>
        public async Task<bool> UpdateArtistAsync(Artist artist)
        {            
            try
            {                
                ValidateArtist(artist);
                var existingArtist = await _context.Artists.FindAsync(artist.Id);
                if (existingArtist == null)
                {
                    return false;
                }
                
                // 更新艺人信息
                _context.Entry(existingArtist).CurrentValues.SetValues(artist);
                await _context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"更新艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除艺人（同步）
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteArtist(int id)
        {            
            try
            {                
                var artist = _context.Artists.Find(id);
                if (artist == null)
                {
                    return false;
                }
                
                // 删除关联的参与关系
                var participations = _context.Participations.Where(p => p.ArtistId == id).ToList();
                _context.Participations.RemoveRange(participations);
                
                // 删除艺人
                _context.Artists.Remove(artist);
                _context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除艺人失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除艺人（异步）
        /// </summary>
        /// <param name="id">艺人ID</param>
        /// <returns>是否删除成功</returns>
        public async Task<bool> DeleteArtistAsync(int id)
        {            
            try
            {                
                var artist = await _context.Artists.FindAsync(id);
                if (artist == null)
                {
                    return false;
                }
                
                // 删除关联的参与关系
                var participations = _context.Participations.Where(p => p.ArtistId == id).ToList();
                _context.Participations.RemoveRange(participations);
                
                // 删除艺人
                _context.Artists.Remove(artist);
                await _context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除艺人失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region 电影相关操作

        /// <summary>
        /// 添加电影（同步）
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>添加的电影ID</returns>
        public int AddMovie(Movie movie)
        {            
            try
            {                
                ValidateMovie(movie);
                _context.Movies.Add(movie);
                _context.SaveChanges();
                return movie.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 添加电影（异步）
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>添加的电影ID</returns>
        public async Task<int> AddMovieAsync(Movie movie)
        {            
            try
            {                
                ValidateMovie(movie);
                await _context.Movies.AddAsync(movie);
                await _context.SaveChangesAsync();
                return movie.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有电影（同步）
        /// </summary>
        /// <returns>电影列表</returns>
        public List<Movie> GetAllMovies()
        {            
            try
            {                
                return _context.Movies.ToList();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有电影（异步）
        /// </summary>
        /// <returns>电影列表</returns>
        public async Task<List<Movie>> GetAllMoviesAsync()
        {            
            try
            {                
                return await _context.Movies.ToListAsync();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取电影（同步）
        /// </summary>
        /// <param name="id">电影ID</param>
        /// <returns>电影对象，如果不存在则返回null</returns>
        public Movie GetMovieById(int id)
        {            
            try
            {                
                return _context.Movies.FirstOrDefault(m => m.Id == id);
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据ID获取电影（异步）
        /// </summary>
        /// <param name="id">电影ID</param>
        /// <returns>电影对象，如果不存在则返回null</returns>
        public async Task<Movie> GetMovieByIdAsync(int id)
        {            
            try
            {                
                return await _context.Movies.FirstOrDefaultAsync(m => m.Id == id);
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据评级获取电影列表（同步）
        /// </summary>
        /// <param name="minRating">最低评级</param>
        /// <returns>电影列表</returns>
        public List<Movie> GetMoviesByRating(float minRating)
        {            
            try
            {                
                return _context.Movies.Where(m => m.Rating >= minRating && m.Rating.HasValue).ToList();
            }
            catch (Exception ex)
            {                
                throw new Exception($"根据评级获取电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据评级获取电影列表（异步）
        /// </summary>
        /// <param name="minRating">最低评级</param>
        /// <returns>电影列表</returns>
        public async Task<List<Movie>> GetMoviesByRatingAsync(float minRating)
        {            
            try
            {                
                return await _context.Movies.Where(m => m.Rating >= minRating && m.Rating.HasValue).ToListAsync();
            }
            catch (Exception ex)
            {                
                throw new Exception($"根据评级获取电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新电影信息（同步）
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateMovie(Movie movie)
        {            
            try
            {                
                ValidateMovie(movie);
                var existingMovie = _context.Movies.Find(movie.Id);
                if (existingMovie == null)
                {
                    return false;
                }
                
                // 更新电影信息
                _context.Entry(existingMovie).CurrentValues.SetValues(movie);
                _context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"更新电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新电影信息（异步）
        /// </summary>
        /// <param name="movie">电影对象</param>
        /// <returns>是否更新成功</returns>
        public async Task<bool> UpdateMovieAsync(Movie movie)
        {            
            try
            {                
                ValidateMovie(movie);
                var existingMovie = await _context.Movies.FindAsync(movie.Id);
                if (existingMovie == null)
                {
                    return false;
                }
                
                // 更新电影信息
                _context.Entry(existingMovie).CurrentValues.SetValues(movie);
                await _context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"更新电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除电影（同步）
        /// </summary>
        /// <param name="id">电影ID</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteMovie(int id)
        {            
            try
            {                
                var movie = _context.Movies.Find(id);
                if (movie == null)
                {
                    return false;
                }
                
                // 删除关联的参与关系
                var participations = _context.Participations.Where(p => p.MovieId == id).ToList();
                _context.Participations.RemoveRange(participations);
                
                // 删除电影
                _context.Movies.Remove(movie);
                _context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除电影失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除电影（异步）
        /// </summary>
        /// <param name="id">电影ID</param>
        /// <returns>是否删除成功</returns>
        public async Task<bool> DeleteMovieAsync(int id)
        {            
            try
            {                
                var movie = await _context.Movies.FindAsync(id);
                if (movie == null)
                {
                    return false;
                }
                
                // 删除关联的参与关系
                var participations = _context.Participations.Where(p => p.MovieId == id).ToList();
                _context.Participations.RemoveRange(participations);
                
                // 删除电影
                _context.Movies.Remove(movie);
                await _context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除电影失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region 参与关系相关操作

        /// <summary>
        /// 添加艺人参与电影关系（同步）
        /// </summary>
        /// <param name="participation">参与关系对象</param>
        /// <returns>添加的参与关系ID</returns>
        public int AddParticipation(Participation participation)
        {            
            try
            {                
                ValidateParticipation(participation);
                _context.Participations.Add(participation);
                _context.SaveChanges();
                return participation.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加参与关系失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 添加艺人参与电影关系（异步）
        /// </summary>
        /// <param name="participation">参与关系对象</param>
        /// <returns>添加的参与关系ID</returns>
        public async Task<int> AddParticipationAsync(Participation participation)
        {            
            try
            {                
                ValidateParticipation(participation);
                await _context.Participations.AddAsync(participation);
                await _context.SaveChangesAsync();
                return participation.Id;
            }
            catch (Exception ex)
            {                
                throw new Exception($"添加参与关系失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取电影的所有演员（同步）
        /// </summary>
        /// <param name="movieId">电影ID</param>
        /// <returns>演员列表</returns>
        public List<Artist> GetArtistsByMovie(int movieId)
        {            
            try
            {                
                return _context.Artists
                    .Join(
                        _context.Participations,
                        artist => artist.Id,
                        participation => participation.ArtistId,
                        (artist, participation) => new { Artist = artist, Participation = participation }
                    )
                    .Where(ap => ap.Participation.MovieId == movieId)
                    .Select(ap => ap.Artist)
                    .ToList();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影演员列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取电影的所有演员（异步）
        /// </summary>
        /// <param name="movieId">电影ID</param>
        /// <returns>演员列表</returns>
        public async Task<List<Artist>> GetArtistsByMovieAsync(int movieId)
        {            
            try
            {                
                return await _context.Artists
                    .Join(
                        _context.Participations,
                        artist => artist.Id,
                        participation => participation.ArtistId,
                        (artist, participation) => new { Artist = artist, Participation = participation }
                    )
                    .Where(ap => ap.Participation.MovieId == movieId)
                    .Select(ap => ap.Artist)
                    .ToListAsync();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取电影演员列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取艺人参演的所有电影（同步）
        /// </summary>
        /// <param name="artistId">艺人ID</param>
        /// <returns>电影列表</returns>
        public List<Movie> GetMoviesByArtist(int artistId)
        {            
            try
            {                
                return _context.Movies
                    .Join(
                        _context.Participations,
                        movie => movie.Id,
                        participation => participation.MovieId,
                        (movie, participation) => new { Movie = movie, Participation = participation }
                    )
                    .Where(mp => mp.Participation.ArtistId == artistId)
                    .Select(mp => mp.Movie)
                    .ToList();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人参演电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取艺人参演的所有电影（异步）
        /// </summary>
        /// <param name="artistId">艺人ID</param>
        /// <returns>电影列表</returns>
        public async Task<List<Movie>> GetMoviesByArtistAsync(int artistId)
        {            
            try
            {                
                return await _context.Movies
                    .Join(
                        _context.Participations,
                        movie => movie.Id,
                        participation => participation.MovieId,
                        (movie, participation) => new { Movie = movie, Participation = participation }
                    )
                    .Where(mp => mp.Participation.ArtistId == artistId)
                    .Select(mp => mp.Movie)
                    .ToListAsync();
            }
            catch (Exception ex)
            {                
                throw new Exception($"获取艺人参演电影列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除参与关系（同步）
        /// </summary>
        /// <param name="id">参与关系ID</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteParticipation(int id)
        {            
            try
            {                
                var participation = _context.Participations.Find(id);
                if (participation == null)
                {
                    return false;
                }
                
                _context.Participations.Remove(participation);
                _context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除参与关系失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除参与关系（异步）
        /// </summary>
        /// <param name="id">参与关系ID</param>
        /// <returns>是否删除成功</returns>
        public async Task<bool> DeleteParticipationAsync(int id)
        {            
            try
            {                
                var participation = await _context.Participations.FindAsync(id);
                if (participation == null)
                {
                    return false;
                }
                
                _context.Participations.Remove(participation);
                await _context.SaveChangesAsync();
                return true;
            }
            catch (Exception ex)
            {                
                throw new Exception($"删除参与关系失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region 验证方法

        /// <summary>
        /// 验证艺人对象
        /// </summary>
        /// <param name="artist">艺人对象</param>
        private void ValidateArtist(Artist artist)
        {            
            if (artist == null)
            {
                throw new ArgumentNullException(nameof(artist), "艺人对象不能为空");
            }
            
            if (string.IsNullOrWhiteSpace(artist.Name))
            {
                throw new ArgumentException("艺人名不能为空", nameof(artist.Name));
            }
            
            if (artist.Name.Length > 100)
            {
                throw new ArgumentException("艺人名长度不能超过100个字符", nameof(artist.Name));
            }
            
            if (artist.Sex != 'M' && artist.Sex != 'F' && artist.Sex != 'O')
            {
                throw new ArgumentException("性别必须为'M'、'F'或'O'", nameof(artist.Sex));
            }
        }

        /// <summary>
        /// 验证电影对象
        /// </summary>
        /// <param name="movie">电影对象</param>
        private void ValidateMovie(Movie movie)
        {            
            if (movie == null)
            {
                throw new ArgumentNullException(nameof(movie), "电影对象不能为空");
            }
            
            if (string.IsNullOrWhiteSpace(movie.Name))
            {
                throw new ArgumentException("电影名称不能为空", nameof(movie.Name));
            }
            
            if (movie.Name.Length > 100)
            {
                throw new ArgumentException("电影名称长度不能超过100个字符", nameof(movie.Name));
            }
            
            if (movie.Duration <= 0)
            {
                throw new ArgumentException("电影时长必须大于0", nameof(movie.Duration));
            }
            
            if (movie.Rating.HasValue && (movie.Rating < 0 || movie.Rating > 10))
            {
                throw new ArgumentException("电影评级必须在0-10之间", nameof(movie.Rating));
            }
        }

        /// <summary>
        /// 验证参与关系对象
        /// </summary>
        /// <param name="participation">参与关系对象</param>
        private void ValidateParticipation(Participation participation)
        {            
            if (participation == null)
            {
                throw new ArgumentNullException(nameof(participation), "参与关系对象不能为空");
            }
            
            if (participation.ArtistId <= 0)
            {
                throw new ArgumentException("艺人ID必须大于0", nameof(participation.ArtistId));
            }
            
            if (participation.MovieId <= 0)
            {
                throw new ArgumentException("电影ID必须大于0", nameof(participation.MovieId));
            }
            
            // 检查艺人是否存在
            if (!_context.Artists.Any(a => a.Id == participation.ArtistId))
            {
                throw new ArgumentException($"艺人ID {participation.ArtistId} 不存在", nameof(participation.ArtistId));
            }
            
            // 检查电影是否存在
            if (!_context.Movies.Any(m => m.Id == participation.MovieId))
            {
                throw new ArgumentException($"电影ID {participation.MovieId} 不存在", nameof(participation.MovieId));
            }
            
            // 检查是否已存在相同的参与关系
            if (_context.Participations.Any(p => 
                p.ArtistId == participation.ArtistId && 
                p.MovieId == participation.MovieId && 
                p.Id != participation.Id))
            {
                throw new InvalidOperationException("该艺人已经参与了这部电影");
            }
        }

        #endregion

        #region IDisposable 实现

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {            
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing">是否释放托管资源</param>
        protected virtual void Dispose(bool disposing)
        {            
            if (!_disposed)
            {
                if (disposing)
                {
                    _context?.Dispose();
                }
                _disposed = true;
            }
        }

        #endregion
    }
}